﻿<%@ WebHandler Language="C#" Class="SIPO.Web.AjaxHandler.Transaction.recInvoicehandler" %>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;

using SIPO.Library.DataAccess.Transaction;
using SIPO.Library.DomainObject.Transaction;
using SIPO.Library.DataAccess.Administration;
using SIPO.Library.DomainObject.Administration;

using SIPO.Library.DomainObject.Administration.Catalog;
using SIPO.Library.DataAccess.Administration.Catalog;

using SIPO.Library.Enum;
using SIPO.Web.Support;

using Newtonsoft.Json.Linq;

namespace SIPO.Web.AjaxHandler.Transaction
{

    public class recInvoicehandler : SIPO.Web.BaseHandler
    {

        private recInvoiceRepo recInvoiceDal = new recInvoiceRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSubSectorRepo recSubSectorDal = new recSubSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private recSectorRepo recSectorDal = new recSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

        private recLogRepo logDal = new recLogRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
        private string MethodName = string.Empty;

        public override void ProcessRequest(HttpContext context)
        {
            base.ProcessRequest(context);

            MethodName = context.Request.Params["method"];

            context.Response.Clear();
            context.Response.Buffer = true;

            System.IO.StreamReader sr = new System.IO.StreamReader(context.Request.InputStream);

            string jsonString = string.Empty;
            jsonString = context.Server.UrlDecode(sr.ReadToEnd());
            JObject jObj = string.IsNullOrEmpty(jsonString) ? null : JObject.Parse(jsonString);

            sr.Close();
            sr.Dispose();

            switch (MethodName.ToLower())
            {
                case ("insert"):
                    context.Response.Write(Insert(context, jObj));
                    break;
                case ("update"):
                    context.Response.Write(Update(context, jObj));
                    break;
                case ("delete"):
                    context.Response.Write(Delete(context, jObj));
                    break;
                case ("getdata"):
                    context.Response.Write(GetData(context));
                    break;
                case ("getdatareport"):
                    context.Response.Write(GetDataReport(context));
                    break;
                case ("getdatahistoryreport"):
                    context.Response.Write(GetDataHistoryReport(context));
                    break;
                case ("getdatabybankid"):
                    context.Response.Write(GetDataByBankID(context));
                    break;

                default:
                    // do nothing
                    break;
            }

        }

        public override bool IsReusable
        {
            get
            {
                return false;
            }
        }


        private string Insert(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                recBillingRepo recBillingDal = new recBillingRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                recInvoiceDetailRepo recInvoiceDetailDal = new recInvoiceDetailRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                List<recInvoiceDetail> listInvoiceDtl = new List<recInvoiceDetail>();
                recInvoiceDetail objInvoiceDtl = new recInvoiceDetail();

                var strId = (string)jo["BillingNo"];
                string[] ids = strId.Split(',');
                decimal InvoiceValue = 0;
                int DebtorSectorID = 0;

                string sectorCode = string.Empty;
                string subsectorCode = string.Empty;

                if (context.Request.Params["sectorCode"] != null)
                    sectorCode = context.Request.Params["sectorCode"].ToString().Trim();

                if (context.Request.Params["subsectorCode"] != null)
                    subsectorCode = context.Request.Params["subsectorCode"].ToString().Trim();


                foreach (string id in ids)
                {
                    //get data recBilling
                    recBilling objBilling = recBillingDal.GetById(id);

                    if (objBilling != null)
                    {
                        InvoiceValue += objBilling.Value;
                        objInvoiceDtl = new recInvoiceDetail();
                        objInvoiceDtl.Value = objBilling.Value;
                        objInvoiceDtl.TransTypeID = objBilling.TransTypeID;
                        objInvoiceDtl.TariffID = objBilling.TariffID;
                        objInvoiceDtl.DueDate = objBilling.DueDate;
                        objInvoiceDtl.ForPayment = objBilling.ForPayment;
                        objInvoiceDtl.BillingNo = objBilling.BillingNo;
                        objInvoiceDtl.CreatedDate = DateTime.Now;
                        objInvoiceDtl.CreatedBy = base.UserLoggedIn;

                        // ambil debtorsectorID buat InvoiceHeader
                        DebtorSectorID = objBilling.DebtorSectorID;

                        listInvoiceDtl.Add(objInvoiceDtl);

                    }
                }
                recInvoice objBeSpelled = recInvoiceDal.GetBeSpelledByInvoiceValue(InvoiceValue);
                recInvoice obj = new recInvoice();
                obj.InvoiceNo = sectorCode + "." + subsectorCode + "." + DateTime.Now.ToString("MM") + "." + DateTime.Now.ToString("yy") + "." + recInvoiceDal.GetMaxID(sectorCode, subsectorCode).ToString().PadLeft(5, '0');
                obj.InvoiceDate = DateTime.ParseExact(DateTime.Now.ToString("dd/MM/yyyy"), "dd/MM/yyyy", new System.Globalization.CultureInfo("id-ID"));
                obj.InvoiceValue = InvoiceValue;
                //ambil bank ID dari subsector
                recSector objSector = recSectorDal.GetBySectorCode(Convert.ToInt32(sectorCode));
                recSubSector objSubSector = recSubSectorDal.GetBySubSectorCode(Convert.ToInt32(subsectorCode),objSector.SectorID);
                if (objSubSector != null)
                {
                    obj.BankID = (short) objSubSector.BankID;
                }
                //end
                obj.BeSpelled = objBeSpelled == null ? string.Empty : objBeSpelled.BeSpelled;
                obj.DebtorSectorID = DebtorSectorID;
                obj.CreatedDate = DateTime.Now;
                obj.CreatedBy = base.UserLoggedIn;
                obj.Balance = InvoiceValue;
                recInvoiceDal.Save(obj, listInvoiceDtl);
                helper.Message = "Data berhasil di cetak surat setoran  !";
                helper.IsSuccess = true;
                helper.Data = obj.InvoiceNo;
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Insert";
                log.LogMessage = helper.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }



        private string Update(HttpContext context, JObject jo)
        {
            JSONHelper helper = new JSONHelper();

            try
            {
                recInvoice obj = new recInvoice();
                obj.InvoiceNo = (string)jo["InvoiceNo"];
                obj.InvoiceDate = DateTime.ParseExact(jo["InvoiceDate"].ToString(), "dd/MM/yyyy", new System.Globalization.CultureInfo("id-ID"));
                obj.InvoiceValue = (decimal)jo["InvoiceValue"];
                obj.BankID = (Int16)jo["BankID"];
                obj.BeSpelled = (string)jo["BeSpelled"];
                obj.PaymentNo = (string)jo["PaymentNo"];
                obj.PaymentDate = DateTime.ParseExact(jo["PaymentDate"].ToString(), "dd/MM/yyyy", new System.Globalization.CultureInfo("id-ID"));
                obj.PaymentValue = (decimal)jo["PaymentValue"];
                obj.IsHapusBuku = false;
                obj.IsHapusTagih = false;
                obj.DebtorSectorID = (int)jo["DebtorSectorID"];

                obj.UpdatedDate = DateTime.Now;
                obj.UpdatedBy = base.UserLoggedIn;

                recInvoiceDal.Update(obj);

                helper.Message = "Data berhasil di ubah !";
                helper.IsSuccess = true;
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Update";
                log.LogMessage = helper.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }


        private string Delete(HttpContext context, JObject jo)
        {
            recInvoiceDetailRepo recInvoiceDetailDal = new recInvoiceDetailRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);

            JSONHelper helper = new JSONHelper();
            try
            {


                var strId = (string)jo["InvoiceNo"];

                List<recInvoiceDetail> listDetails = recInvoiceDetailDal.GetListByInvoiceNo(jo["InvoiceNo"].ToString().Trim());
                List<recInvoiceDetail> listInvoiceDetail = new List<recInvoiceDetail>();
                foreach (recInvoiceDetail data in listDetails)
                {
                    recInvoiceDetail objInvoiceDetail = new recInvoiceDetail();
                    objInvoiceDetail.BillingNo = data.BillingNo;
                    listInvoiceDetail.Add(objInvoiceDetail);
                }

                recInvoice objInvoice = new recInvoice();
                objInvoice.InvoiceNo = (string)jo["InvoiceNo"];
                recInvoiceDal.Delete(objInvoice, listInvoiceDetail);

                //var strId = (string)jo["InvoiceDetailID"];

                //string[] ids = strId.Split(',');
                //foreach (string id in ids)
                //{
                //    int intID = Convert.ToInt32(id);
                //    //ambil data Invoice Detail
                //     objInvoiceDtl = recInvoiceDetailDal.GetById(intID);
                //    if (objInvoiceDtl != null)
                //    {
                //        //ambil data Invoice Header base No Invoice dari tabel detail Invoice
                //        if (objInvoiceDtl.InvoiceNo != null && objInvoiceDtl.InvoiceNo.Trim() != "")
                //        {
                //             objInvoice = recInvoiceDal.GetById(objInvoiceDtl.InvoiceNo);
                //            //maping data invoice header dari invoice detail
                //            if (objInvoice != null)
                //            {
                //                objInvoice.InvoiceNo = objInvoice.InvoiceNo;
                //                objInvoice.InvoiceValue = objInvoice.InvoiceValue - objInvoiceDtl.Value;
                //                objInvoice.UpdatedBy = base.UserLoggedIn;
                //                objInvoice.UpdatedDate = DateTime.Now;
                //            }
                //        }


                //        //ambil data Billing base No Billing dari tabel detail Invoice
                //        if (objInvoiceDtl.BillingNo != null && objInvoiceDtl.BillingNo.Trim() != "")
                //        {
                //             objBilling = recBillingDal.GetById(objInvoiceDtl.BillingNo);
                //            if (objBilling != null)
                //            {
                //                objBilling.BillingNo = objBilling.BillingNo;
                //                //objBilling.InvoiceDetailID = null;
                //                //objBilling.InvoiceNo = null;
                //                objBilling.UpdatedBy = base.UserLoggedIn;
                //                objBilling.UpdatedDate = DateTime.Now;

                //                recInvoiceDal.Delete(objInvoice, objInvoiceDtl, objBilling);
                //            }
                //        }


                //    }

                //    //recInvoiceDal.Delete(intID);
                //}

                helper.IsSuccess = true;
                helper.Message = "Data berhasil di hapus !";
            }
            catch (Exception ex)
            {
                helper.IsSuccess = false;
                helper.Message = ex.Message;
            }
            finally
            {
                recLog log = new recLog();
                log.LogEvent = "Delete";
                log.LogMessage = helper.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);

            }

            return Newtonsoft.Json.JsonConvert.SerializeObject(helper);

        }

        private string GetData(HttpContext context)
        {


            string parameters = Helper.JqGridFilterToSQLParams(context);

            if (base.DebtorID != null && base.DebtorID != 0)
                parameters = parameters == string.Empty ? " WHERE recDebtorSector.DebtorID=" + base.DebtorID : parameters + " AND recDebtorSector.DebtorID=" + base.DebtorID;

            if (base.SubSubSectorIDs != null)
                parameters = parameters == string.Empty ? " WHERE recDebtorSector.SubSubSectorID IN (" + base.SubSubSectorIDs + ")" : parameters + " AND recDebtorSector.SubSubSectorID IN (" + base.SubSubSectorIDs + ")";


            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recInvoice.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recInvoice> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recInvoiceDal.SelectScalar(SQL.Function.Aggregate.Count, "recInvoice.InvoiceNo", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recInvoiceDal.GetList(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetData";
                log.LogMessage = ex.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }

        private string GetDataByBankID(HttpContext context)
        {


            string parameters = Helper.JqGridFilterToSQLParams(context);
            
            string queryRegistrasi = string.Empty;
            string queryInvoice = string.Empty;
            string queryAksi = string.Empty;
           
                    queryRegistrasi = @"select A.DebtorSectorID
                    ,a.InvoiceNo
                    ,a.InvoiceDate
                    ,a.InvoiceValue
                    ,a.BankID
                    ,a.BeSpelled
                    ,a.PaymentNo
                    ,a.PaymentDate
                    ,a.PaymentValue
                    ,a.IsHapusBuku
                    ,a.IsHapusTagih
                    ,a.CreatedDate
                    ,a.CreatedBy
                    ,a.UpdatedDate
                    ,a.UpdatedBy
                    ,a.IsCancel
                    ,a.Balance
                    ,recRegistration.PreDebtorName DebtorName
                    from transactions.recInvoice a 
                    inner join transactions.recbilling b on a.InvoiceNo = b.InvoiceNo
                    inner join transactions.recSectorRegistration c ON a.InvoiceNo = c.InvoiceNo 
                    inner join master.recUserSector on c.SectorRegistrationID = recUserSector.RegisterSectorID
                    LEFT JOIN master.recSector ON c.SectorID = recSector.SectorID
                    LEFT JOIN master.recSubSector ON c.SubSectorID = recSubSector.SubSectorID
                    LEFT JOIN master.recSubSubSector ON c.SubSubSectorID = recSubSubSector.SubSubSectorID
                    LEFT JOIN transactions.recInvoice ON a.InvoiceNo = recInvoice.InvoiceNo
                    LEFT JOIN transactions.recRegistration ON c.RegistrationID = recRegistration.RegistrationID
                    where a.debtorsectorid = 0 and b.transtypeid = 1
                    AND a.paymentno is null AND a.BankID = @BankID";


                    queryInvoice = @"	SELECT 
                                        recInvoice.DebtorSectorID
                                        ,recInvoice.InvoiceNo
                                        ,recInvoice.InvoiceDate
                                        ,recInvoice.InvoiceValue
                                        ,recInvoice.BankID
                                        ,recInvoice.BeSpelled
                                        ,recInvoice.PaymentNo
                                        ,recInvoice.PaymentDate
                                        ,recInvoice.PaymentValue
                                        ,recInvoice.IsHapusBuku
                                        ,recInvoice.IsHapusTagih
                                        ,recInvoice.CreatedDate
                                        ,recInvoice.CreatedBy
                                        ,recInvoice.UpdatedDate
                                        ,recInvoice.UpdatedBy
                                        ,recInvoice.IsCancel
                                        ,recInvoice.Balance
                                        ,recDebtor.DebtorName
                                        FROM transactions.recInvoice 
                                        LEFT join transactions.recSectorRegistration  on recInvoice.InvoiceNo = recSectorRegistration.InvoiceNo
                                        LEFT join master.recUserSector on recSectorRegistration.SectorRegistrationID = recUserSector.RegisterSectorID
                                        LEFT JOIN master.recDebtorSector ON recInvoice.DebtorSectorID=recDebtorSector.DebtorSectorID
                                        LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                        LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
                                        LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
                                        LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                        
                                        WHERE recInvoice.DebtorSectorID<>0 
                                        AND recInvoice.PaymentNo IS NULL AND recInvoice.BankID = @BankID ";           
            
                
                //int bankid = Convert.ToInt32(context.Request.Params["bankid"]);

                if (base.DebtorID != null)
                {
                    queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                    queryInvoice = queryInvoice + " AND recdebtor.DebtorID = " + base.DebtorID;
                }
                else
                {
                    if (base.UserTypeID == 1)
                    {
                        queryRegistrasi = queryRegistrasi + "  AND recUserSector.UserID =" + base.UserID;
                        queryInvoice = queryInvoice + " AND recdebtor.DebtorID IS NULL ";
                    }
                    else
                    {
                        queryAksi = @"
                                        select A.DebtorSectorID
                                        ,A.InvoiceNo
                                        ,A.InvoiceDate
                                        ,A.InvoiceValue
                                        ,A.BankID
                                        ,A.BeSpelled
                                        ,A.PaymentNo
                                        ,A.PaymentDate
                                        ,A.PaymentValue
                                        ,A.IsHapusBuku
                                        ,A.IsHapusTagih
                                        ,A.CreatedDate
                                        ,A.CreatedBy
                                        ,A.UpdatedDate
                                        ,A.UpdatedBy
                                        ,A.IsCancel
                                        ,A.Balance 
                                        ,recCorporateAction.CorporateName DebtorName 
                                        from transactions.recInvoice a inner join transactions.recbilling b on a.InvoiceNo = b.InvoiceNo
                                        inner join transactions.recCorporateAction  on b.InvoiceNo = recCorporateAction.InvoiceNo
                                        LEFT JOIN master.recDebtorSector ON a.DebtorSectorID=recDebtorSector.DebtorSectorID
                                        LEFT JOIN master.recSubSubSector ON recSubSubSector.SubSubSectorID =recDebtorSector.SubSubSectorID
                                        LEFT JOIN master.recSector ON recDebtorSector.SectorID=recSector.SectorID
                                        LEFT JOIN master.recSubSector ON recDebtorSector.SubSectorID=recSubSector.SubSectorID
                                        LEFT JOIN master.recDebtor ON recDebtorSector.DebtorID = recDebtor.DebtorID
                                        where a.debtorsectorid = 0 and b.transtypeid = 4
                                        and a.paymentno is null AND a.BankID = @BankID ";

                    }
                }


                if (base.SubSubSectorIDs != null)
                {

                    //filter SectorID
                    queryInvoice = queryInvoice + @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            )";


                    queryRegistrasi = queryRegistrasi + @" AND recSector.SectorID IN ( 
                            SELECT  recSector.SectorID
                            FROM master.recSector
                            INNER JOIN master.recSubSector ON recSector.SectorID = recSubSector.SectorID
                            inner join master.recSubSubSector on recSubSubSector.SubSectorID=recSubSector.SubSectorID
                            WHERE recSubSubSector.SubSubSectorID IN(" + base.SubSubSectorIDs + @")
                            GROUP BY recSector.SectorID
                            )";


                    // filter SubSectorID
                    queryInvoice = queryInvoice + @" AND recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )";


                    queryRegistrasi = queryRegistrasi + @" AND recSubSector.SubSectorID IN (
                            SELECT DISTINCT recSubSector.SubSectorID
                            FROM master.recSubSector INNER JOIN master.recSubSubSector ON recSubSector.SubSectorID = recSubSubSector.SubSectorID
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSector.SubSectorID
                            )";

                    //filter SubSubSectorID
                    queryInvoice = queryInvoice + @" AND recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) ";

                    queryRegistrasi = queryRegistrasi + @" AND recSubSubSector.SubSubSectorID IN (
                            SELECT DISTINCT recSubSubSector.SubSubSectorID
                            FROM master.recSubSubSector 
                            WHERE  recSubSubSector.SubSubSectorID IN (" + base.SubSubSectorIDs + @")
                            GROUP BY recSubSubSector.SubSubSectorID
                            ) ";

                }

                int bankid =0;
                
              if (context.Request.Params["bankID"] != null)
                {
                    bankid = Convert.ToInt32(context.Request.Params["bankID"]);
                }

            //if (base.DebtorID != null && base.DebtorID != 0)
            //    parameters = parameters == string.Empty ? " WHERE AA.DebtorID=" + base.DebtorID : parameters + " AND AA.DebtorID=" + base.DebtorID;

            //if (base.SubSubSectorIDs != null)
            //    parameters = parameters == string.Empty ? " WHERE AA.SubSubSectorID IN (" + base.SubSubSectorIDs + ")" : parameters + " AND AA.SubSubSectorID IN (" + base.SubSubSectorIDs + ")";

            //if (context.Request.Params["bankID"] != null)
            //    parameters = parameters == string.Empty ? " WHERE AA.BankID IN (" + context.Request.Params["bankID"].ToString().Trim() + ")" : parameters + " AND AA.BankID IN (" + context.Request.Params["bankID"].ToString().Trim() + ")";

            //parameters = parameters == string.Empty ? " WHERE recInvoice.PaymentNo IS NULL AND ISNULL(InvoiceValue,0)-ISNULL(PaymentValue,0)>0 " : parameters + " AND recInvoice.PaymentNo IS NULL AND ISNULL(InvoiceValue,0)-ISNULL(PaymentValue,0)>0";

            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recInvoice.Fields.DebtorSectorID : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recInvoice> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recInvoiceDal.SelectScalarUpdatePembayaran(SQL.Function.Aggregate.Count,recInvoice.Fields.InvoiceNo,queryInvoice,queryAksi,queryRegistrasi,parameters,bankid));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recInvoiceDal.GetListUpdatePembayaran(pageSize, page, sidx, sord,queryInvoice,queryAksi,queryRegistrasi,parameters,bankid);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetData";
                log.LogMessage = ex.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);

        }

        public string GetDataReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);

            DateTime? periodeAwal = context.Request["PeriodeAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            DateTime? periodeAkhir = context.Request["PeriodeAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            int debtorID = context.Request["DebtorID"].Equals(string.Empty) ? 0 : int.Parse(context.Request["DebtorID"].ToString());

            //konversi tanggal
            string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAwal);
            string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAkhir);

            // filter debtorID
            if (debtorID != 0)
            {
                parameters += "where recDebtor.DebtorID = ISNULL (" + debtorID + ", recDebtor.DebtorID)";
            }
            //filter invoice date
            if (debtorID == 0)
            {
                if (!sPeriodeAwal.Equals(string.Empty) && !periodeAkhir.Equals(string.Empty))
                {
                    parameters += "WHERE recInvoice.InvoiceDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recInvoice.InvoiceDate) AND ISNULL ('" + sPeriodeAkhir + "', recInvoice.InvoiceDate)";
                }
            }
            else if (!periodeAwal.Equals(null) && !periodeAkhir.Equals(null))
            {
                parameters += " AND recInvoice.InvoiceDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recInvoice.InvoiceDate) AND ISNULL ('" + sPeriodeAkhir + "', recInvoice.InvoiceDate)";
            }


            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recInvoice.Fields.InvoiceNo : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recInvoice> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recInvoiceDal.SelectScalarReport(SQL.Function.Aggregate.Count, "recInvoice.InvoiceNo", parameters));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recInvoiceDal.GetListReport(pageSize, page, sidx, sord, parameters);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }
        public string GetDataHistoryReport(HttpContext context)
        {
            string parameters = Helper.JqGridFilterToSQLParams(context);
            string parametersInvoiceNo = string.Empty;


            DateTime? periodeAwal = context.Request["PeriodeAwal"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAwal"], Helper.Culture.Indonesian);
            DateTime? periodeAkhir = context.Request["PeriodeAkhir"].Equals(string.Empty) ? (DateTime?)null : Helper.FormatDate(context.Request["PeriodeAkhir"], Helper.Culture.Indonesian);
            // int debtorID = context.Request["DebtorID"].Equals(string.Empty) ? 0 : int.Parse(context.Request["DebtorID"].ToString());

            //konversi tanggal
            string sPeriodeAwal = periodeAwal == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAwal);
            string sPeriodeAkhir = periodeAkhir == null ? string.Empty : string.Format("{0:yyyyMMdd}", periodeAkhir);


            if (!periodeAwal.Equals(null) && !periodeAkhir.Equals(null))
            {
                if (base.DebtorID != (int?)null)
                {
                    recDebtorSectorRepo recDebtorSectorDal = new recDebtorSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    recSectorRegistrationRepo recSectorRegistrationDal = new recSectorRegistrationRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    List<recDebtorSector> debtorsectors = recDebtorSectorDal.GetById(base.DebtorID.Value, base.SubSubSectorIDs);
                    List<recSectorRegistration> sectorRegistrations = recSectorRegistrationDal.GetList(base.RegistrationID, base.DebtorID.Value, base.SubSubSectorIDs);
                    int i = 0;
                    string DSid = string.Empty;
                    string invoiceNos = string.Empty;
                    foreach (recDebtorSector DS in debtorsectors)
                    {
                        if (i == 0)
                            DSid += DS.DebtorSectorID;
                        else
                            DSid += "," + DS.DebtorSectorID.ToString();
                        i++;
                    }
                    int j = 0;
                    foreach (recSectorRegistration sectorRegistration in sectorRegistrations)
                    {
                        if (j == 0)
                            invoiceNos += "'" + sectorRegistration.InvoiceNo + "'";
                        else
                            invoiceNos += "," + "'" + sectorRegistration.InvoiceNo.ToString() + "'";
                        j++;
                    }
                    if (parameters.ToString() == "")
                        parameters += "where transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)  and transactions.recBilling.debtorsectorid in(" + DSid + ")";
                    else
                        parameters += "and (transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)  and transactions.recBilling.debtorsectorid in(" + DSid + "))";
                    if (sectorRegistrations.Count > 0)
                        parametersInvoiceNo = "where transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)  and transactions.recBilling.InvoiceNo in (" + invoiceNos + ")";
                    else
                    {
                        invoiceNos += "'0'";
                        parametersInvoiceNo = "where transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)  and transactions.recBilling.InvoiceNo in (" + invoiceNos + ")";
                    }
                }
                else
                {
                    if (parameters.ToString() == "")
                        parameters += "where transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)";
                    else
                        parameters += "and transactions.recBilling.BillingDate BETWEEN ISNULL ('" + sPeriodeAwal + "', recBilling.BillingDate) AND ISNULL ('" + sPeriodeAkhir + "', recBilling.BillingDate)";
                }
            }
            else
            {
                if (base.DebtorID != (int?)null)
                {
                    int registrationid = base.RegistrationID;
                    recDebtorSectorRepo recDebtorSectorDal = new recDebtorSectorRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    recSectorRegistrationRepo recSectorRegistrationDal = new recSectorRegistrationRepo(ConfigurationManager.ConnectionStrings["SIPOConn"].ConnectionString);
                    List<recDebtorSector> debtorsectors = recDebtorSectorDal.GetById(base.DebtorID.Value, base.SubSubSectorIDs);
                    List<recSectorRegistration> sectorRegistrations = recSectorRegistrationDal.GetList(base.RegistrationID, base.DebtorID.Value, base.SubSubSectorIDs);
                    int i = 0;
                    string DSid = string.Empty;
                    string invoiceNos = string.Empty;
                    foreach (recDebtorSector DS in debtorsectors)
                    {
                        if (i == 0)
                            DSid += DS.DebtorSectorID;
                        else
                            DSid += "," + DS.DebtorSectorID.ToString();
                        i++;
                    }
                    int j = 0;
                    foreach (recSectorRegistration sectorRegistration in sectorRegistrations)
                    {
                        if (j == 0)
                            invoiceNos += "'" + sectorRegistration.InvoiceNo + "'";
                        else
                            invoiceNos += "," + "'" + sectorRegistration.InvoiceNo.ToString() + "'";
                        j++;
                    }
                    if (parameters.ToString() == "")
                        parameters += "where transactions.recBilling.debtorsectorid in(" + DSid + ")";
                    else
                        parameters += "and transactions.recBilling.debtorsectorid in(" + DSid + ")";
                    if (sectorRegistrations.Count > 0)
                        parametersInvoiceNo = "where transactions.recBilling.InvoiceNo in (" + invoiceNos + ")";
                    else
                    {
                        invoiceNos += "'0'";
                        parametersInvoiceNo = "where transactions.recBilling.InvoiceNo in (" + invoiceNos + ")";
                    }
                }

            }


            int page = 1;
            int.TryParse(context.Request.QueryString["page"] ?? "1", out page);

            int pageSize = 10;
            int.TryParse(context.Request.QueryString["rows"] ?? "10", out pageSize);

            string sidx = ((context.Request.QueryString["sidx"]) == null ? recInvoice.Fields.InvoiceNo : context.Request.QueryString["sidx"].ToString());

            string sord = ((context.Request.QueryString["sord"]) == null ? "ASC" : context.Request.QueryString["sord"].ToString());

            int totalCount = 0;
            int totalPages = 0;
            List<recInvoice> pagedList = null;

            try
            {
                totalCount = Convert.ToInt32(recInvoiceDal.SelectScalarHistoryReport(SQL.Function.Aggregate.Count, "recbilling.billingno", parameters, parametersInvoiceNo));

                decimal dc = decimal.Ceiling(Convert.ToDecimal(totalCount) / Convert.ToDecimal(pageSize));
                totalPages = Convert.ToInt32(dc);

                pagedList = recInvoiceDal.GetListHistoryReport(pageSize, page, "date", sord, parameters, parametersInvoiceNo);
            }
            catch (Exception ex)
            {
                recLog log = new recLog();
                log.LogEvent = "GetDataHistoryReport";
                log.LogMessage = ex.Message;
                log.LogSource = "recInvoicehandler.ashx";
                log.CreatedBy = base.UserLoggedIn;
                log.CreatedDate = DateTime.Now;
                logDal.Save(log);
                log.Dispose();
            }
            finally
            {

            }

            // send params into jqGrid's format
            var gridData = new
            {
                page = page,
                total = totalPages,
                records = totalCount,
                rows = pagedList,
            };

            return Newtonsoft.Json.JsonConvert.SerializeObject(gridData);
        }

    }

}